package operation;

import User.User;
import Utils.JDBCUtils;
import Utils.SQLSentencePoll;
import org.apache.poi.hssf.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;

/**
 * 数据库学生表信息导入到本地Excel文件功能实现类
 */
public class StudentInfoExport implements IOperation{
    @Override
    public boolean operate(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        FileOutputStream os = null;
        try {
            System.out.println("正在导出所有学生信息...");
            //创建工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建表对象
            HSSFSheet sheet = workbook.createSheet("学生信息");
            sheet.setDefaultColumnWidth(15); //设置表默认列宽
            //获取连接
            conn = JDBCUtils.getConnection();
            //预编译sql
            ps = conn.prepareStatement(SQLSentencePoll.AllAccountsInfoDisplay);
            //执行查询，处理返回结果集
            rs = ps.executeQuery();
            //获取结果集元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount(); //获取列长
            //生成表头
            int rowNum = 0;
            HSSFRow row = sheet.createRow(rowNum++);
            row.setHeight((short) 350);
            for (int i = 0; i < columnCount; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(rsmd.getColumnLabel(i+1));
            }
            //生成表数据行
            while (rs.next()){
                row = sheet.createRow(rowNum++);
                for (int i = 0; i < columnCount; i++) {
                    HSSFCell cell = row.createCell(i);
                    cell.setCellValue(rs.getString(i+1));
                }
            }
            //写出文件
            String path = "C:\\Users\\Jiali\\Desktop\\导出信息.xls"; //指明写出的文件路径
            os = new FileOutputStream(path);
            workbook.write(os);
            System.out.println("信息已导入桌面名为\"导出信息.xls\"的Excel文件中！");
        } catch (SQLException | IOException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            JDBCUtils.closeResource(conn,ps,rs);
            try {
                if (os != null){
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return true;
    }
}
